package dao;

import bean.Activity;
import bean.PageBean;
import util.Connect;
import util.JdbcUtils;
import util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author yl
 */
public class ActivityDao {

    /**
     * 查看活动
     *
     * @param pageBean
     * @param activity
     * @return
     * @throws Exception
     */
    public List<Object> activityList(PageBean pageBean, Activity activity, String afterCreateTime, String beforeCreateTime, String afterStartTime, String beforeStartTime, String afterEndTime, String beforeEndTime) throws Exception {
        StringBuilder sb = new StringBuilder("select activityId, u.userId as userId, title, createTime, startTime, endTime, description, u.status as status, u.replyContent, replyTime, tempCount, maxUserCount, click, a.userName as userName from activity u inner join user a on u.userId=a.userId");
        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(activity.getActivityId())) {
            sb.append(" and u.activityId =?");
            para.add(activity.getActivityId());
        }
        if (StringUtil.isNotEmpty(activity.getUserId())) {
            sb.append(" and u.userId =?");
            para.add(activity.getUserId());
        }
        if (StringUtil.isNotEmpty(activity.getTitle())) {
            sb.append(" and u.title like ?");
            para.add("%" + activity.getTitle() + "%");
        }

        if (StringUtil.isNotEmpty(afterCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)>=TO_SECONDS(?)");
            para.add(afterCreateTime);
        }
        if (StringUtil.isNotEmpty(beforeCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)<=TO_SECONDS(?)");
            para.add(beforeCreateTime);
        }

        if (StringUtil.isNotEmpty(afterStartTime)) {
            sb.append(" and TO_SECONDS(u.startTime)>=TO_SECONDS(?)");
            para.add(afterStartTime);
        }
        if (StringUtil.isNotEmpty(beforeStartTime)) {
            sb.append(" and TO_SECONDS(u.startTime)<=TO_SECONDS(?)");
            para.add(beforeStartTime);
        }

        if (StringUtil.isNotEmpty(afterEndTime)) {
            sb.append(" and TO_SECONDS(u.endTime)>=TO_SECONDS(?)");
            para.add(afterEndTime);
        }
        if (StringUtil.isNotEmpty(beforeEndTime)) {
            sb.append(" and TO_SECONDS(u.endTime)<=TO_SECONDS(?)");
            para.add(beforeEndTime);
        }

        if (StringUtil.isNotEmpty(activity.getDescription())) {
            sb.append(" and u.description like ?");
            para.add("%" + activity.getDescription() + "%");
        }
        if (StringUtil.isNotEmpty(activity.getStatus())) {
            sb.append(" and u.status =?");
            para.add(activity.getStatus());
        }
        if (StringUtil.isNotEmpty(activity.getReplyContent())) {
            sb.append(" and u.replyContent like ?");
            para.add("%" + activity.getReplyContent() + "%");
        }
        if (StringUtil.isNotEmpty(activity.getTempCount())) {
            sb.append(" and u.tempCount =?");
            para.add(activity.getTempCount());
        }
        if (StringUtil.isNotEmpty(activity.getMaxUserCount())) {
            sb.append(" and u.maxUserCount =?");
            para.add(activity.getMaxUserCount());
        }
        sb.append(" order by u.startTime desc");
        if (pageBean != null) {
            sb.append(" limit " + pageBean.getStart() + "," + pageBean.getRows());
        }
        String sql = sb.toString();
        return JdbcUtils.queryForArrObject(sql, para, Activity.class);
    }

    /**
     * 删除活动
     *
     * @param delIds
     * @return
     * @throws Exception
     */
    public int activityDelete(String delIds) throws Exception {
        String sql = "delete from activity where activityId in(?)";
        List<Object> para = new ArrayList<Object>();
        para.add(delIds);
        return JdbcUtils.update(sql, para);
    }

    /**
     * 添加活动
     *
     * @param activity
     * @return
     * @throws Exception
     */
    public boolean activityAdd(Activity activity) throws Exception {
        StringBuilder sb = new StringBuilder("insert into activity values(");
        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(activity.getActivityId())) {
            sb.append(" ,?");
            para.add(activity.getActivityId());
        } else if (activity.getActivityId() == null) {
            sb.append(" ,null");
        }
        if (StringUtil.isNotEmpty(activity.getUserId())) {
            sb.append(" ,?");
            para.add(activity.getUserId());
        }
        if (StringUtil.isNotEmpty(activity.getTitle())) {
            sb.append(" ,?");
            para.add(activity.getTitle());
        }
        if (StringUtil.isNotEmpty(activity.getCreateTime())) {
            sb.append(" ,?");
            para.add(activity.getCreateTime());
        }
        if (StringUtil.isNotEmpty(activity.getStartTime())) {
            sb.append(" ,?");
            para.add(activity.getStartTime());
        }
        if (StringUtil.isNotEmpty(activity.getEndTime())) {
            sb.append(" ,?");
            para.add(activity.getEndTime());
        }
        if (StringUtil.isNotEmpty(activity.getDescription())) {
            sb.append(" ,?");
            para.add(activity.getDescription());
        }
        if (StringUtil.isNotEmpty(activity.getStatus())) {
            sb.append(" ,?");
            para.add(activity.getStatus());
        }
        if (StringUtil.isNotEmpty(activity.getReplyContent())) {
            sb.append(" ,?");
            para.add(activity.getReplyContent());
        } else if (activity.getReplyContent() == null) {
            sb.append(" ,null");
        }

        if (StringUtil.isNotEmpty(activity.getReplyTime())) {
            sb.append(" ,?");
            para.add(activity.getReplyTime());
        } else if (activity.getReplyTime() == null) {
            sb.append(" ,null");
        }

        if (StringUtil.isNotEmpty(activity.getTempCount())) {
            sb.append(" ,?");
            para.add(activity.getTempCount());
        }
        if (StringUtil.isNotEmpty(activity.getMaxUserCount())) {
            sb.append(" ,?");
            para.add(activity.getMaxUserCount());
        }
        if (StringUtil.isNotEmpty(activity.getClick())) {
            sb.append(" ,?");
            para.add(activity.getClick());
        }

        sb.append(")");
        String sql = sb.toString().replaceFirst(",", " ");
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * 修改活动
     *
     * @param activity
     * @return
     * @throws Exception
     */
    public int activityModify(Activity activity) throws Exception {
        StringBuilder sb = new StringBuilder("update activity u ");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(activity.getUserId())) {
            sb.append(" , u.userId=?");
            para.add(activity.getUserId());
        }
        if (StringUtil.isNotEmpty(activity.getTitle())) {
            sb.append(" , u.title =?");
            para.add(activity.getTitle());
        }
        if (StringUtil.isNotEmpty(activity.getCreateTime())) {
            sb.append(" , u.createTime =?");
            para.add(activity.getCreateTime());
        }
        if (StringUtil.isNotEmpty(activity.getStartTime())) {
            sb.append(" , u.startTime =?");
            para.add(activity.getStartTime());
        }
        if (StringUtil.isNotEmpty(activity.getEndTime())) {
            sb.append(" , u.endTime =?");
            para.add(activity.getEndTime());
        }
        if (StringUtil.isNotEmpty(activity.getDescription())) {
            sb.append(" , u.description =?");
            para.add(activity.getDescription());
        }
        if (StringUtil.isNotEmpty(activity.getStatus())) {
            sb.append(" , u.status =?");
            para.add(activity.getStatus());
        }
        if (StringUtil.isNotEmpty(activity.getReplyContent())) {
            sb.append(" , u.replyContent=?");
            para.add(activity.getReplyContent());
        }
        if (StringUtil.isNotEmpty(activity.getReplyTime())) {
            sb.append(" , u.replyTime=?");
            para.add(activity.getReplyTime());
        }
        if (StringUtil.isNotEmpty(activity.getTempCount())) {
            sb.append(" , u.tempCount=?");
            para.add(activity.getTempCount());
        }
        if (StringUtil.isNotEmpty(activity.getMaxUserCount())) {
            sb.append(" , u.maxUserCount=?");
            para.add(activity.getMaxUserCount());
        }

        sb.append(" where u.activityId=?");
        para.add(activity.getActivityId());
        String sql = sb.toString().replaceFirst(",", "set");
        return JdbcUtils.update(sql, para);
    }

    public int activityCount(Activity activity, String afterCreateTime, String beforeCreateTime, String afterStartTime, String beforeStartTime, String afterEndTime, String beforeEndTime) throws Exception {
        StringBuffer sb = new StringBuffer("select count(*) as total from activity u");

        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(activity.getActivityId())) {
            sb.append(" and u.activityId =? ");
            para.add(activity.getActivityId());
        }
        if (StringUtil.isNotEmpty(activity.getUserId())) {
            sb.append(" and u.userId =? ");
            para.add(activity.getUserId());
        }
        if (StringUtil.isNotEmpty(activity.getTitle())) {
            sb.append(" and u.title like ?");
            para.add("%" + activity.getTitle() + "%");
        }
        if (StringUtil.isNotEmpty(afterCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)>=TO_SECONDS(?)");
            para.add(afterCreateTime);
        }
        if (StringUtil.isNotEmpty(beforeCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)<=TO_SECONDS(?)");
            para.add(beforeCreateTime);
        }

        if (StringUtil.isNotEmpty(afterStartTime)) {
            sb.append(" and TO_SECONDS(u.startTime)>=TO_SECONDS(?)");
            para.add(afterStartTime);
        }
        if (StringUtil.isNotEmpty(beforeStartTime)) {
            sb.append(" and TO_SECONDS(u.startTime)<=TO_SECONDS(?)");
            para.add(beforeStartTime);
        }

        if (StringUtil.isNotEmpty(afterEndTime)) {
            sb.append(" and TO_SECONDS(u.endTime)>=TO_SECONDS(?)");
            para.add(afterEndTime);
        }
        if (StringUtil.isNotEmpty(beforeEndTime)) {
            sb.append(" and TO_SECONDS(u.endTime)<=TO_SECONDS(?)");
            para.add(beforeEndTime);
        }
        if (StringUtil.isNotEmpty(activity.getDescription())) {
            sb.append(" and u.description like ?");
            para.add("%" + activity.getDescription() + "%");
        }
        if (StringUtil.isNotEmpty(activity.getStatus())) {
            sb.append(" and u.status=?");
            para.add(activity.getStatus());
        }
        if (StringUtil.isNotEmpty(activity.getReplyContent())) {
            sb.append(" and u.replyContent like ?");
            para.add("%" + activity.getReplyContent() + "%");
        }
        if (StringUtil.isNotEmpty(activity.getTempCount())) {
            sb.append(" and u.tempCount=?");
            para.add(activity.getTempCount());
        }
        if (StringUtil.isNotEmpty(activity.getMaxUserCount())) {
            sb.append(" and u.maxUserCount=?");
            para.add(activity.getMaxUserCount());
        }

        if (StringUtil.isNotEmpty(activity.getClick())) {
            sb.append(" and u.click=?");
            para.add(activity.getClick());
        }
        String sql = sb.toString().replaceFirst("and", "where");
        List<Map<String, String>> result = JdbcUtils.queryForMap(sql, para);

        if (!result.isEmpty()) {
            return Integer.parseInt(result.get(0).get("total"));
        } else {
            return 0;
        }
    }

    public int findUnProcessAdviceCount() throws SQLException {
        String sql = "SELECT count(*) AS num FROM activity WHERE status IN (-1, 1)";
        Connection connection = Connect.getInstance().getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet set = preparedStatement.executeQuery();
        int r = 0;
        if (set.next()) {
            r = set.getInt("num");
        }
        Connect.getInstance().freeConnection(connection);
        Connect.getInstance().freeStatement(preparedStatement);
        Connect.getInstance().freeResultSet(set);
        return r;
    }

    public List<Object> findUnProcessAdvice(PageBean page) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT activityId, a.userId, title, createTime, startTime, endTime, description, a.`status`, replyContent, replyTime, tempCount, maxUserCount, click, u.userName AS userName FROM activity a INNER JOIN user u ON a.userId = u.userId WHERE a.status IN (-1, 1) order by a.startTime desc");
        if (page != null) {
            sqlBuilder.append("limit ")
                .append(page.getStart())
                .append(",")
                .append(page.getRows());
        }
        return JdbcUtils.queryForArrObject(sqlBuilder.toString(), null, Activity.class);
    }

    /*public List<Map<String, String>> activityClick(String activityId) throws Exception{
        String sql ="update activity set click=click+1 where activityId=?";

        List<Object> para = new ArrayList<Object>();
        if(StringUtil.isNotEmpty(activityId)){
            para.add(activityId);
        }
        return JdbcUtils.queryForMap(sql, para);
    }*/

    public int activityClick(String activityId) throws Exception {
        String sql = "update activity set click=click+1 where activityId=" + activityId;
        return JdbcUtils.update(sql, null);
    }
}
